package cn.edu.hbwe.book.dao.impl;

import java.sql.Connection;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import cn.edu.hbwe.book.dao.BookDao;
import cn.edu.hbwe.book.entity.Book;
import cn.edu.hbwe.book.utils.DuridUtil;




public class BookDaoImpl implements BookDao{

	public List<Book> findAll() {
		List<Book> books= new ArrayList<Book>();
		ResultSetHandler<List<Book>> h=new BeanListHandler<Book>(Book.class);
		QueryRunner qr=new QueryRunner();
				try {			
					Connection conn=DuridUtil.getConn();
					String sql="select * from products";
					books=qr.query(conn, sql, h);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			
			return books;
			
		}

	public void deleteBook(String id) {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner();
		try {			
			Connection conn=DuridUtil.getConn();
			String sql="delete from products where id=?";
			int i=qr.update(conn, sql, id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
	}

	public List<Book> findBooksbyCondition(String id,String name,String category,String minprice,String maxprice) {
		// TODO Auto-generated method stub
		List<Book> books= new ArrayList<Book>();
		ResultSetHandler<List<Book>> h=new BeanListHandler<Book>(Book.class);
			List<Object> list = new ArrayList<Object>();
			Connection conn=DuridUtil.getConn();
			String sql = "select * from products where 1=1 ";
		
			QueryRunner runner=new QueryRunner();

			if (id != null && id.trim().length() > 0) {
				sql += " and id like ?";
				list.add(id);
			}

			if (name != null && name.trim().length() > 0) {
				sql += " and name like ?";
				list.add(name);
			}
			if (category != null && category.trim().length() > 0) {
				sql += " and category=?";
				list.add(category);
			}
			if (minprice != null && maxprice != null
					&& minprice.trim().length() > 0 && maxprice.trim().length() > 0) {
				sql += " and price between ? and ?";
				list.add(minprice);
				list.add(maxprice);
			}
			try {
				Object[] params = list.toArray();
				books=runner.query(conn, sql, h,params);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return books;
			
		
	}	
	public void addbookList(Book book) {
		QueryRunner qr=new QueryRunner();
		try {
			String sql = "insert into products(id,name,price,category,pnum,imgurl,description) values(?,?,?,?,?,?,?)";
			Connection conn=DuridUtil.getConn();
			qr.update(conn, sql,book.getId(),book.getName(),book.getPrice(),book.getCategory(),book.getPnum(),book.getImgurl(),book.getDescription());//数据自己补
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
	}

	public void updateBookMessage(String id,Book book) {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner();
		try {
			String sql="update products set name=?,price=?,category=?,pnum=?,imgurl=?,description=? where id=?";
			Connection conn=DuridUtil.getConn();
			qr.update(conn, sql,book.getName(),book.getPrice(),book.getCategory(),book.getPnum(),book.getImgurl(),book.getDescription(),id);//数据自己补
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}	
	
	public  Book Findone(String id){
		
		Book book=null;
		ResultSetHandler<Book> h=new BeanHandler<Book>(Book.class);
		QueryRunner qr=new QueryRunner();
			try {
				String sql="select * from products where id=?";
				Connection conn=DuridUtil.getConn();
				book=qr.query(conn,sql, h,id);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		
		
		return book;
	}	
}


